﻿Imports System.Math
Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class RelatorioProfactory
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        Try
            'If Not Session("Logged") Then
            '    Session("ShowSessionExpired") = 1 ' Show lblSessionExpired in Login.aspx
            '    Response.Redirect("Login.aspx")
            'End If

            If Not Page.IsPostBack Then


                ' Header
                lblModulo.Text = "Relatórios"
                lblModuloItem.Text = "Relatório Profactory"

                Dim strConnection As String
                strConnection = "A"


                FillDDLCompetencia()


            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Protected Sub btnImprimir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImprimir.Click

        ImprimirRelatorio()

    End Sub

    Private Sub FillDDLCompetencia()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT MesAnoDeCompetencia," & _
                             " LEFT(MesAnoDeCompetencia,2) + '.' + RIGHT(MesAnoDeCompetencia,4) AS Competencia" & _
                             " FROM Competencia" & _
                             " WHERE RIGHT(MesAnoDeCompetencia,4) >= 2011" & _
                             " ORDER BY RIGHT(MesAnoDeCompetencia,4) + LEFT(MesAnoDeCompetencia,2) DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompetencia.DataTextField = "Competencia"
                ddlCompetencia.DataValueField = "MesAnoDeCompetencia"
                ddlCompetencia.DataSource = mydatatable
                ddlCompetencia.DataBind()

                ddlCompetencia.Items.Insert(0, "")

                If Not (ddlCompetencia.Items.Count = 0) Then
                    ddlCompetencia.SelectedIndex = 1
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "FillDDLCompetencia: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub ImprimirRelatorio()

        'mail_trade = 0 'Inseriu prazo onde dia solicitado pelo usuário é menor ou igual que no cadastro plano de vendas (tb_Prazo_Adicional)
        'Enviar e-mail para Master Data e Gerentes

        Dim NewScript As String
        Dim tot As Double = 0
        Dim nDataCreated As String

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim strNomeDoArquivo As String
        Dim strFlag01 As Boolean

        Dim ItemList As New ArrayList()
        Dim strDespesa As String
        Dim i As Long
        Dim y As Long
        Dim strcontador As Long

        Dim strOrdemCompra As String
        Dim strFatura As String
        Dim strRazaoSocialFornecedorServico As String
        Dim strCodigoDaHolding As String
        Dim strCodigoDaCia As String
        Dim strCodigoDoEstabelecimento As String
        Dim strPesoLiquido, strPesoBruto As String
        Dim strCodIncoterm As String
        Dim strDscCondicaoPagamento As String
        Dim strNumeroDocumento As String
        Dim strDI As String
        Dim strDscMeioTransporte As String
        Dim strValorFob As String
        Dim strValorRealFrete As String
        Dim strValorRealSeguro As String
        Dim strSISCOMEX As String
        Dim strValorAcrescimo As String
        Dim strAdicao As String
        Dim strDataRegistro As String
        Dim strValorII As String
        Dim strValorIPI As String
        Dim strValorPIS As String
        Dim strValorCOFINS As String
        Dim strValorICMSNotificado As String
        Dim strValorTaxaConversao As String
        Dim strValorTaxaMoedaOriginal As String

        Dim strValorUSFob As String
        Dim strValorUSFrete As String
        Dim strValorUSSeguro As String
        Dim strValorUSAcrescimo As String
        Dim strValorTotalUS As String
        Dim strValorTotalReal As String
        Dim strValorRealFob As String
        Dim strValorRealAcrescimo As String

        strFatura = ""
        strRazaoSocialFornecedorServico = ""
        strCodigoDaHolding = ""
        strCodigoDaCia = ""
        strCodigoDoEstabelecimento = ""
        strPesoLiquido = ""
        strPesoBruto = ""
        strCodIncoterm = ""
        strDscCondicaoPagamento = ""
        strNumeroDocumento = ""
        strDI = ""
        strDscMeioTransporte = ""
        strValorFob = ""
        strValorRealFrete = ""
        strValorRealSeguro = ""
        strSISCOMEX = ""
        strValorAcrescimo = ""
        strAdicao = ""
        strDataRegistro = ""
        strValorII = ""
        strValorIPI = ""
        strValorPIS = ""
        strValorCOFINS = ""
        strValorICMSNotificado = ""
        strValorTaxaConversao = ""
        strValorTaxaMoedaOriginal = ""
        strValorUSFob = ""
        strValorUSFrete = ""
        strValorUSSeguro = ""
        strValorUSAcrescimo = ""
        strValorTotalUS = ""
        strValorTotalReal = ""
        strValorRealFob = ""
        strValorRealAcrescimo = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR, CONVERT(varchar, getdate(), 108) AS TIMEDOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                    Session("dDTHora") = Trim(theSqlDataReader(1))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nDataCreated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        strNomeDoArquivo = "RelProfactory_" & nAnoCreated & nMesCreated & nDiaCreated & Session("dDTHora").Replace(":", "") & ".XLS"


        'If (IsDate(nDataCreated)) Then
        '    nDataCreated = "'" & nAnoCreated & "/" & nMesCreated & "/" & nDiaCreated & "'"
        'Else
        '    nDataCreated = "Null"
        'End If

        If (IsDate(nDataCreated)) Then
            nDataCreated = "'" & FormatMediumDate(nDataCreated) & "'"
        Else
            nDataCreated = "Null"
        End If

        nDataCreated = "'" & nAnoCreated & "/" & nMesCreated & "/" & nDiaCreated & "'"

        'Script de envio de e-mail para TRADE **************************************************************************************************

        'Deleta arquivo SOPA.xls da pasta do servidor
        IO.File.Delete(Server.MapPath(strNomeDoArquivo))

        NewScript = "<br>"
        NewScript = NewScript & ""
        NewScript = NewScript & "<br>"
        NewScript = NewScript & "<table border='1' align='Center' fontcolor='white'>"
        NewScript = NewScript & "<td style='width=100px;background=# 99CC00'><b>PO</b></td>"
        NewScript = NewScript & "</script>"

        NewScript = NewScript & "<td style='width=500px; background=# 99CC00'><b>Invoice</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>NF Transfer</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Data NF Transfer</b></td>"
        NewScript = NewScript & "<td style='width=500px; background=# 99CC00'><b>Numero Documento</b></td>"
        NewScript = NewScript & "<td style='width=500px; background=# 99CC00'><b>CNJ</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Cod. Item</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Desc. Item</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Modal</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Incoterm</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Cond. Pagamento Client</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>NCM</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Qtd</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Unid.</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Peso Bruto</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Peso Liquido</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Num. DI </b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Num. Adicao</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Data da DI</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Icms</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Ipi</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Pis</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Cofins</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>US$</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>EURO</b></td>"

        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Valor FOB</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Valor Frete</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Valor Seguro</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Capatazia</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Valor Aduan</b></td>"

        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>FOB R$</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Frete R$</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Seguro R$</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Tx SISCOMEX</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Capatazias - Acrescimo R$</b></td>"
        NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>%II</b></td>"
        'NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Despesa</b></td>"
        'NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>Valor</b></td>"

        Using theSqlConnection2 As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString2 As String
            theQueryString2 = "SELECT DISTINCT C.DscTipoDespesa " & _
                    "FROM  custoimportacao A, custoimportacaodata B, tipodespesa C " & _
                    "WHERE A.CodigoDaHolding = B.CodigoDaHolding And " & _
                    "A.CodigoDaCia = B.CodigoDaCia AND " & _
                    "A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento AND " & _
                    "A.CodFornecedorServico = B.CodFornecedorServico AND " & _
                    "A.NumeroDocumento = B.NumeroDocumento AND " & _
                    "B.CodTipoDespesa = C.CodTipoDespesa AND B.CodTipoOperacao = C.CodTipoOperacao AND A.MesAnoDeCompetencia = '" & ddlCompetencia.SelectedValue & "' " & _
                    "ORDER BY C.DscTipoDespesa"
            Dim theSqlCommand2 As SqlCommand = theSqlConnection2.CreateCommand()
            theSqlCommand2.CommandText = theQueryString2
            Try
                theSqlConnection2.Open()
                Dim theSqlDataReader2 As SqlDataReader = theSqlCommand2.ExecuteReader()
                Do While theSqlDataReader2.Read()
                    If Not (ItemList.Contains(theSqlDataReader2!DscTipoDespesa)) Then
                        ItemList.Add(theSqlDataReader2!DscTipoDespesa)
                    End If
                    NewScript = NewScript & "<td style='width=200px; background=# 99CC00'><b>" & theSqlDataReader2!DscTipoDespesa & "</b></td>"
                Loop
                theSqlDataReader2.Close()
            Catch ex As Exception
            End Try
        End Using


        NewScript = NewScript & "</tr> "

        'Dim vCodigoDoFornecedor As String
        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT MesAnoDeCompetencia, Centro, Pedido, PedidoAgregado, Fornecedor, Importador, Referencia, Item, Material, TextoBreveMaterial, TMv, DtLancamento, Quantidade, UMB, MontanteMI, Validated " & _
                            "FROM ImportMB51 " & _
                            "WHERE MesAnoDeCompetencia = '" & ddlCompetencia.SelectedValue & "' AND Centro = 4920 " & _
                            "ORDER BY Referencia, Item"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                tot = 0
                Do While theSqlDataReader.Read()
                    If Not (IsDBNull(Trim(theSqlDataReader(0)))) Then

                        NewScript = NewScript & "<tr>"
                        NewScript = NewScript & "<td style='width:50px'>"
                        NewScript = NewScript & theSqlDataReader!PedidoAgregado
                        NewScript = NewScript & "</td>"

                        'Consulta tabelas de Custo de Importação Fatura Header e Datail
                        Using theSqlConnection2 As New SqlConnection(Conexao_liion.theConnectionString)
                            Dim theQueryString2 As String
                            theQueryString2 = "SELECT A.CodigoDaHolding, A.CodigoDaCia, A.CodigoDoEstabelecimento, B.OrdemCompra, A.Fatura, A.NumeroDocumento, A.MesAnoDeCompetencia, A.DataFatura, A.QtdeItems, A.ValorTotalItems, A.ValorDespesasEmbarque, A.ValorTotalFatura, A.CodSituacaoFatura, A.CodFornecedor, A.CodigoDaMoeda, A.CodImportador, A.CodIncoterm, A.CodCondicaoPagamento, C.DscCondicaoPagamento, " & _
                                        "B.LinhaFatura, B.Adicao, B.SeqOrdemCompra, B.LinhaOrdemCompra, B.CodNaturezaOperacao, B.CodItem, B.QtdeFatura, B.CodUnidadeMedidaCompra, B.PesoLiquido, B.ValorUnitarioItem, B.ValorTotalItem, B.ValorDespesasEmbarque, B.ValorTotalItemFatura, B.CodSituacaoFatura, B.CodigoDaMoeda " & _
                                        "FROM  custofaturaheader A,  custofaturadetail B, condicaopagamento C " & _
                                        "WHERE A.CodigoDaHolding = B.CodigoDaHolding And " & _
                                        "A.CodigoDaCia = B.CodigoDaCia And " & _
                                        "A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento And " & _
                                        "A.Fatura = B.Fatura And " & _
                                        "A.CodCondicaoPagamento = C.CodCondicaoPagamento AND " & _
                                        "B.OrdemCompra = '" & theSqlDataReader!PedidoAgregado & "' " & _
                                        "ORDER BY A.NumeroDocumento"
                            Dim theSqlCommand2 As SqlCommand = theSqlConnection2.CreateCommand()
                            theSqlCommand2.CommandText = theQueryString2
                            Try
                                theSqlConnection2.Open()
                                Dim theSqlDataReader2 As SqlDataReader = theSqlCommand2.ExecuteReader()
                                If (theSqlDataReader2.Read() = True) Then
                                    strOrdemCompra = theSqlDataReader2!OrdemCompra
                                    strFatura = theSqlDataReader2!Fatura
                                    strCodigoDaHolding = theSqlDataReader2!CodigoDaHolding
                                    strCodigoDaCia = theSqlDataReader2!CodigoDaCia
                                    strCodigoDoEstabelecimento = theSqlDataReader2!CodigoDoEstabelecimento
                                    strCodIncoterm = theSqlDataReader2!CodIncoterm
                                    strDscCondicaoPagamento = theSqlDataReader2!DscCondicaoPagamento
                                    strNumeroDocumento = theSqlDataReader2!NumeroDocumento
                                    'strValorFob = theSqlDataReader2!ValorTotalItem
                                    strAdicao = theSqlDataReader2!Adicao
                                End If
                                theSqlDataReader2.Close()
                            Catch ex As Exception
                            End Try
                        End Using
                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strFatura
                        NewScript = NewScript & "</td>"

                        'Consulta tabelas de Custo de Importação Header e Datail
                        Using theSqlConnection2 As New SqlConnection(Conexao_liion.theConnectionString)
                            Dim theQueryString2 As String
                            theQueryString2 = "SELECT A.CodigoDaHolding, A.CodigoDaCia, A.CodigoDoEstabelecimento, A.CodFornecedorServico, A.NumeroDocumento, A.DataDocumento, A.DataLancamento, A.DI, A.PesoLiquido, A.DataRegistro, A.CodMoeda, A.CodMeioTransporte, E.DscMeioTransporte, A.ValorTaxaMoedaOriginal, A.ValorTaxaConversao, A.ValorOriginal, A.ValorReal, A.ValorUS, A.PercentualParticipacao, A.ValorFob, A.CodMoedaFob, A.ValorTaxaFob, A.ValorRealFob, A.ValorUSFob, A.PercentFob, A.ValorFrete, A.CodMoedaFrete, A.ValorTaxaFrete, A.ValorRealFrete, A.ValorUSFrete, A.PercentFrete, A.ValorSeguro, A.CodMoedaSeguro, A.ValorTaxaSeguro, A.ValorRealSeguro, A.ValorUSSeguro, A.PercentSeguro, A.ValorAcrescimo, A.CodMoedaAcrescimo, A.ValorTaxaAcrescimo, A.ValorRealAcrescimo, A.ValorUSAcrescimo, A.PercentAcrescimo, A.ValorTotalReal, A.ValorTotalUS, A.ValorTotalPercent, A.ValorII, A.ValorIPI, A.ValorPIS, A.ValorCOFINS, A.ValorICMSNotificado, A.ValorUSII, A.ValorUSIPI, A.ValorUSPIS, A.ValorUSCOFINS, A.ValorUSICMSNotificado, A.CodTipoTributacaoII, A.CodTipoTributacaoIPI, A.CodTipoTributacaoPIS, A.CodTipoTributacaoCOFINS, A.CodTipoTributacaoICMS, A.CodTipoTributacaoICMSST, A.VlTotDespesas, A.VlTotDespesaUSD, A.VlDescontoComercial, A.VlDebitos, A.VlOutrosCreditos, A.VlTaxaTSA, A.VlTaxaFTI, A.VlBCICMS, A.VLICMS, A.SISCOMEX, A.ValorDescontoComercial, A.VlDescontoComercialUSD, A.VlDebitosUSD, A.VlOutrosCreditosUSD, A.VlTaxaTSAUSD, A.VlTaxaFTIUSD, A.SISCOMEXUSD, " & _
                                        "B.CodMoeda, B.LinhaCustoImportacao, B.CodSubContratante, B.CodTipoOperacao, B.CodTipoDespesa, B.ValorDespesa, B.ValorDespesaUSD, C.RazaoSocialFornecedorServico, D.DscTipoDespesa " & _
                                        "FROM  custoimportacao A, custoimportacaodata B, fornecedorservico C, tipodespesa D, meiotransporte E " & _
                                        "WHERE A.CodigoDaHolding = B.CodigoDaHolding And " & _
                                        "A.CodigoDaCia = B.CodigoDaCia And " & _
                                        "A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento And " & _
                                        "A.CodFornecedorServico = B.CodFornecedorServico And " & _
                                        "A.NumeroDocumento = B.NumeroDocumento And " & _
                                        "A.CodFornecedorServico = C.CodFornecedorServico And " & _
                                        "B.CodTipoOperacao = D.CodTipoOperacao And " & _
                                        "B.CodTipoDespesa = D.CodTipoDespesa And " & _
                                        "A.CodMeioTransporte = E.CodMeioTransporte And " & _
                                        "A.NumeroDocumento = " & strNumeroDocumento & " " & _
                                        "ORDER BY A.NumeroDocumento"
                            Dim theSqlCommand2 As SqlCommand = theSqlConnection2.CreateCommand()
                            theSqlCommand2.CommandText = theQueryString2
                            Try
                                theSqlConnection2.Open()
                                Dim theSqlDataReader2 As SqlDataReader = theSqlCommand2.ExecuteReader()
                                If (theSqlDataReader2.Read() = True) Then
                                    strDI = theSqlDataReader2!DI
                                    strRazaoSocialFornecedorServico = theSqlDataReader2!CodFornecedorServico & " - " & theSqlDataReader2!RazaoSocialFornecedorServico
                                    strDscMeioTransporte = theSqlDataReader2!DscMeioTransporte
                                    strValorFob = theSqlDataReader2!ValorFob
                                    strValorRealFrete = theSqlDataReader2!ValorRealFrete
                                    strValorRealSeguro = theSqlDataReader2!ValorRealSeguro
                                    strSISCOMEX = theSqlDataReader2!SISCOMEX
                                    strValorAcrescimo = theSqlDataReader2!ValorAcrescimo
                                    strDataRegistro = theSqlDataReader2!DataRegistro
                                    'strValorII = theSqlDataReader2!ValorII
                                    strValorIPI = theSqlDataReader2!ValorIPI
                                    strValorPIS = theSqlDataReader2!ValorPIS
                                    strValorCOFINS = theSqlDataReader2!ValorCOFINS
                                    strValorICMSNotificado = theSqlDataReader2!ValorICMSNotificado
                                    strValorTaxaConversao = theSqlDataReader2!ValorTaxaConversao
                                    strValorTaxaMoedaOriginal = theSqlDataReader2!ValorTaxaMoedaOriginal

                                    strValorTotalReal = theSqlDataReader2!ValorTotalReal
                                    strValorRealFob = theSqlDataReader2!ValorRealFob

                                    strValorUSFob = theSqlDataReader2!ValorUSFob
                                    strValorUSFrete = theSqlDataReader2!ValorUSFrete
                                    strValorUSSeguro = theSqlDataReader2!ValorUSSeguro
                                    strValorUSAcrescimo = theSqlDataReader2!ValorUSAcrescimo
                                    strValorTotalUS = theSqlDataReader2!ValorTotalUS
                                    strValorRealAcrescimo = theSqlDataReader2!ValorRealAcrescimo
                                    strValorII = theSqlDataReader2!ValorII

                                End If
                                theSqlDataReader2.Close()
                            Catch ex As Exception
                            End Try
                        End Using

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & theSqlDataReader!Referencia
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & theSqlDataReader!DtLancamento
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strNumeroDocumento
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strRazaoSocialFornecedorServico
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & theSqlDataReader!Material
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style=width:100px>"
                        NewScript = NewScript & theSqlDataReader!TextoBreveMaterial
                        NewScript = NewScript & "</td>"

                        Using theSqlConnection2 As New SqlConnection(Conexao_liion.theConnectionString)
                            Dim theQueryString2 As String
                            theQueryString2 = "SELECT PesoLiquido, PesoBruto " & _
                                "FROM item " & _
                                "WHERE CodigoDaHolding = '" & strCodigoDaHolding & "' AND " & _
                                "CodigoDaCia = '" & strCodigoDaCia & "' AND " & _
                                "CodigoDoEstabelecimento = '" & strCodigoDoEstabelecimento & "' AND " & _
                                "CodItem = '" & theSqlDataReader!Material & "'"
                            Dim theSqlCommand2 As SqlCommand = theSqlConnection2.CreateCommand()
                            theSqlCommand2.CommandText = theQueryString2
                            Try
                                theSqlConnection2.Open()
                                Dim theSqlDataReader2 As SqlDataReader = theSqlCommand2.ExecuteReader()
                                If (theSqlDataReader2.Read() = True) Then
                                    strPesoLiquido = theSqlDataReader2!PesoLiquido
                                    strPesoBruto = theSqlDataReader2!PesoBruto
                                End If
                                theSqlDataReader2.Close()
                            Catch ex As Exception
                            End Try
                        End Using



                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strCodIncoterm
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strDscMeioTransporte
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strDscCondicaoPagamento
                        NewScript = NewScript & "</td>"


                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & ""
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style=width:100px>"
                        NewScript = NewScript & theSqlDataReader!Quantidade
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style=width:100px>"
                        NewScript = NewScript & theSqlDataReader!UMB
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style=width:500px>"
                        NewScript = NewScript & strPesoBruto
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:50px'>"
                        NewScript = NewScript & strPesoLiquido
                        NewScript = NewScript & "</td>"


                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strDI
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strAdicao
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strDataRegistro
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorICMSNotificado
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorIPI
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorPIS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorCOFINS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorTaxaConversao
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorTaxaMoedaOriginal
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorUSFob
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorUSFrete
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorUSSeguro
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorUSAcrescimo
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:500px'>"
                        NewScript = NewScript & strValorTotalReal
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strValorRealFob
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strValorRealFrete
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strValorRealSeguro
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strSISCOMEX
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:100px'>"
                        NewScript = NewScript & strValorRealAcrescimo
                        NewScript = NewScript & "</td>"

                        If (strValorII = "") Then
                            NewScript = NewScript & "<td style='width:500px'>"
                            NewScript = NewScript & strValorII
                            NewScript = NewScript & "</td>"
                        Else
                            If (strValorII = "0") Then
                                NewScript = NewScript & "<td style='width:500px'>"
                                NewScript = NewScript & strValorII
                                NewScript = NewScript & "</td>"
                            Else
                                NewScript = NewScript & "<td style='width:500px'>"
                                NewScript = NewScript & strValorII = strValorII / (+strValorTotalReal + strSISCOMEX + strValorRealAcrescimo)
                                NewScript = NewScript & "</td>"
                            End If
                        End If


                        Using theSqlConnection2 As New SqlConnection(Conexao_liion.theConnectionString)
                            Dim theQueryString2 As String
                            theQueryString2 = "SELECT A.CodigoDaHolding, A.CodigoDaCia, A.CodigoDoEstabelecimento, A.CodFornecedorServico, A.NumeroDocumento, A.DataDocumento, A.DataLancamento, A.DI, A.PesoLiquido, A.DataRegistro, A.CodMoeda, A.CodMeioTransporte, E.DscMeioTransporte, A.ValorTaxaMoedaOriginal, A.ValorTaxaConversao, A.ValorOriginal, A.ValorReal, A.ValorUS, A.PercentualParticipacao, A.ValorFob, A.CodMoedaFob, A.ValorTaxaFob, A.ValorRealFob, A.ValorUSFob, A.PercentFob, A.ValorFrete, A.CodMoedaFrete, A.ValorTaxaFrete, A.ValorRealFrete, A.ValorUSFrete, A.PercentFrete, A.ValorSeguro, A.CodMoedaSeguro, A.ValorTaxaSeguro, A.ValorRealSeguro, A.ValorUSSeguro, A.PercentSeguro, A.ValorAcrescimo, A.CodMoedaAcrescimo, A.ValorTaxaAcrescimo, A.ValorRealAcrescimo, A.ValorUSAcrescimo, A.PercentAcrescimo, A.ValorTotalReal, A.ValorTotalUS, A.ValorTotalPercent, A.ValorII, A.ValorIPI, A.ValorPIS, A.ValorCOFINS, A.ValorICMSNotificado, A.ValorUSII, A.ValorUSIPI, A.ValorUSPIS, A.ValorUSCOFINS, A.ValorUSICMSNotificado, A.CodTipoTributacaoII, A.CodTipoTributacaoIPI, A.CodTipoTributacaoPIS, A.CodTipoTributacaoCOFINS, A.CodTipoTributacaoICMS, A.CodTipoTributacaoICMSST, A.VlTotDespesas, A.VlTotDespesaUSD, A.VlDescontoComercial, A.VlDebitos, A.VlOutrosCreditos, A.VlTaxaTSA, A.VlTaxaFTI, A.VlBCICMS, A.VLICMS, A.SISCOMEX, A.ValorDescontoComercial, A.VlDescontoComercialUSD, A.VlDebitosUSD, A.VlOutrosCreditosUSD, A.VlTaxaTSAUSD, A.VlTaxaFTIUSD, A.SISCOMEXUSD, " & _
                                        "B.CodMoeda, B.LinhaCustoImportacao, B.CodSubContratante, B.CodTipoOperacao, B.CodTipoDespesa, B.ValorDespesa, B.ValorDespesaUSD, C.RazaoSocialFornecedorServico, D.DscTipoDespesa " & _
                                        "FROM  custoimportacao A, custoimportacaodata B, fornecedorservico C, tipodespesa D, meiotransporte E " & _
                                        "WHERE A.CodigoDaHolding = B.CodigoDaHolding And " & _
                                        "A.CodigoDaCia = B.CodigoDaCia And " & _
                                        "A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento And " & _
                                        "A.CodFornecedorServico = B.CodFornecedorServico And " & _
                                        "A.NumeroDocumento = B.NumeroDocumento And " & _
                                        "A.CodFornecedorServico = C.CodFornecedorServico And " & _
                                        "B.CodTipoOperacao = D.CodTipoOperacao And " & _
                                        "B.CodTipoDespesa = D.CodTipoDespesa And B.CodTipoOperacao = D.CodTipoOperacao And " & _
                                        "A.CodMeioTransporte = E.CodMeioTransporte And " & _
                                        "A.NumeroDocumento = " & strNumeroDocumento & " AND A.MesAnoDeCompetencia = '" & ddlCompetencia.SelectedValue & "' " & _
                                        "ORDER BY D.DscTipoDespesa"
                            Dim theSqlCommand2 As SqlCommand = theSqlConnection2.CreateCommand()
                            theSqlCommand2.CommandText = theQueryString2
                            Try
                                theSqlConnection2.Open()
                                Dim theSqlDataReader2 As SqlDataReader = theSqlCommand2.ExecuteReader()
                                Dim strColuna As Long
                                strColuna = 0
                                strcontador = 0
                                strFlag01 = True
                                Do While theSqlDataReader2.Read()
                                    For i = strColuna To ItemList.Count - 1
                                        strDespesa = ItemList.Item(i)
                                        If (theSqlDataReader2!DscTipoDespesa = strDespesa) Then
                                            NewScript = NewScript & "<td>"
                                            NewScript = NewScript & theSqlDataReader2!ValorDespesa
                                            NewScript = NewScript & "</td>"
                                            strColuna = i + 1
                                            Exit For
                                        Else
                                            NewScript = NewScript & "<td>"
                                            NewScript = NewScript & "</td>"
                                        End If
                                    Next

                                Loop
                                theSqlDataReader2.Close()
                            Catch ex As Exception
                            End Try
                        End Using

                        NewScript = NewScript & "<tr>"
                        NewScript = NewScript & "</tr>"

                        NewScript = NewScript & "</tr>"

                        'If Not (ItemList_0.Contains(theSqlDataReader!Gerente_email)) Then
                        '    ItemList_0.Add(theSqlDataReader!Gerente_email)
                        'End If

                        'If Not (ItemList_0.Contains(theSqlDataReader!Ger_Regional_email)) Then
                        '    ItemList_0.Add(theSqlDataReader!Ger_Regional_email)
                        'End If

                    End If
                    tot = tot + 1

                    strFatura = ""
                    strRazaoSocialFornecedorServico = ""
                    strCodigoDaHolding = ""
                    strCodigoDaCia = ""
                    strCodigoDoEstabelecimento = ""
                    strPesoLiquido = ""
                    strPesoBruto = ""
                    strCodIncoterm = ""
                    strDscCondicaoPagamento = ""
                    strNumeroDocumento = ""
                    strDI = ""
                    strDscMeioTransporte = ""
                    strValorFob = ""
                    strValorRealFrete = ""
                    strValorRealSeguro = ""
                    strSISCOMEX = ""
                    strValorAcrescimo = ""
                    strAdicao = ""
                    strDataRegistro = ""
                    strValorII = ""
                    strValorIPI = ""
                    strValorPIS = ""
                    strValorCOFINS = ""
                    strValorICMSNotificado = ""
                    strValorTaxaConversao = ""
                    strValorTaxaMoedaOriginal = ""
                    strValorUSFob = ""
                    strValorUSFrete = ""
                    strValorUSSeguro = ""
                    strValorUSAcrescimo = ""
                    strValorTotalUS = ""
                    strValorTotalReal = ""
                    strValorRealFob = ""
                    strValorRealAcrescimo = ""


                Loop
                If tot > 0 Then
                    NewScript = NewScript & "<tr>"
                    NewScript = NewScript & "<td colspan = '5' style='width=500px'><B>"
                    NewScript = NewScript & Format(tot, "##,##0.00")
                    NewScript = NewScript & "</B></td>"
                    NewScript = NewScript & "</tr>"
                End If
                theSqlDataReader.Close()
                theSqlConnection.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        NewScript = NewScript & "</table>"

        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("content-disposition", "attachment;filename=" & strNomeDoArquivo)
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        NewScript = NewScript & "<br><br>"
        NewScript = NewScript & "<p align='Center'> Nota = Este e um documento dinamico do excel</p>"
        HttpContext.Current.Response.Write(NewScript)

        Try
            'Response.Clear()
            Response.AddHeader("content-disposition", "attachment;filename=" & strNomeDoArquivo)
            Response.ContentType = "application/vnd.ms-excel"
            Response.Charset = ""
            Dim textWriter As New IO.StringWriter()
            Dim htmlWriter As New System.Web.UI.HtmlTextWriter(textWriter)
            Response.Write(textWriter.ToString())
            Response.End()
        Catch ex As Exception

        End Try

        'NewScript = NewScript & "</table>"

        'If tot > 0 Then

        '    Dim tw As New System.IO.StreamWriter(Server.MapPath(strNomeDoArquivo), True)
        '    'mailOK_0 = True
        '    tw.Write(NewScript)
        '    tw.Close()

        'End If

        'FIM Script de envio de e-mail para TRADE ***********************************************************************************************

    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function


    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Default.aspx")

    End Sub

    Protected Sub btnPesquisar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPesquisar.Click

    End Sub
End Class
